using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using RTGS.Library;
using RTGS.Library.Entities.Rtgs;
using RTGS.Library.Enum;
using RTGS.Library.Interfaces;

namespace RTGS.Library.DataAccess.Rtgs
	{
		public partial class stpReasonRepo : IRepository<stpReason, string>
		{
			private string errorLog;
			private string connectionString;


			public stpReasonRepo(string ConnectionString)
			{
				errorLog = String.Empty;
				connectionString = ConnectionString;
			}


            public object SelectScalar(SQL.Function.Aggregate function, string column, string parameters)
			{
					object _result = null;
					using (SqlConnection conn = new SqlConnection(connectionString))
					{
					using (SqlCommand cmd = conn.CreateCommand())
					{
							StringBuilder sbQuery = new StringBuilder();
							switch (function)
							{
								case SQL.Function.Aggregate.Max:
									sbQuery.AppendFormat("SELECT MAX({0}) FROM STP_REASON", column);
									break;
								case SQL.Function.Aggregate.Min:
									sbQuery.AppendFormat("SELECT MIN({0}) FROM STP_REASON", column);
									break;
								case SQL.Function.Aggregate.Distinct:
									sbQuery.AppendFormat("SELECT DISTINCT({0}) FROM STP_REASON", column);
									break;
								case SQL.Function.Aggregate.Count:
									sbQuery.AppendFormat("SELECT COUNT({0}) FROM STP_REASON", column);
									break;
								case SQL.Function.Aggregate.Sum:
									sbQuery.AppendFormat("SELECT SUM({0}) FROM STP_REASON", column);
										break;
								case SQL.Function.Aggregate.Avg:
									sbQuery.AppendFormat("SELECT AVG({0}) FROM STP_REASON", column);
									break;
								default:
								// do nothing 
									break;
							}


							cmd.CommandText = sbQuery.ToString();
							cmd.CommandType = CommandType.Text;
							try
							{
								conn.Open();
								_result = cmd.ExecuteScalar();
							}
							catch (Exception ex)
							{
								errorLog = ex.Message;
							}
							finally
							{
								if (conn.State == ConnectionState.Open) conn.Close();
							}
					}
					}
			return _result;
		}


			public List<stpReason> GetList()
			{
				List<stpReason> tt = new List<stpReason>();
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
				using (SqlCommand cmd = conn.CreateCommand())
				{
					cmd.CommandText = "SELECT ReasonID, ReasonDesc FROM STP_REASON";
					cmd.CommandType = CommandType.Text;
					try
						{
							conn.Open();
							using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
							{
								while (dr.Read())
								{
									stpReason t = new stpReason();
									t.reasonid = dr[stpReason.Fields.reasonid] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasonid].ToString();
									t.reasondesc = dr[stpReason.Fields.reasondesc] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasondesc].ToString();


									tt.Add(t);
									t.Dispose();
								}
							}
						
						}
					catch (Exception ex)
					{
						errorLog = ex.Message;
					}
					finally
					{
						if (conn.State == ConnectionState.Open) conn.Close();
					}
			}
		}
			return tt;
		}


            public List<stpReason> GetList(int pageSize, int currentPage, string sortName, string sortOrder, string parameters)
			{
				List<stpReason> tt = new List<stpReason>();
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
					using (SqlCommand cmd = conn.CreateCommand())
					{
						int startRow = ((currentPage - 1) * pageSize);
						int endRow = (startRow + pageSize);
						StringBuilder sbQuery = new StringBuilder();
						sbQuery.AppendFormat(" SELECT * ");
						sbQuery.AppendFormat(" FROM (SELECT TOP({0}) ", pageSize);
						sbQuery.AppendFormat("r.* ");
						sbQuery.AppendFormat("FROM (SELECT TOP({0} + {1} - 1) ReasonID, ReasonDesc", startRow, pageSize);
						sbQuery.AppendFormat(" FROM STP_REASON");
                        sbQuery.AppendFormat("             {0} ", parameters);
						sbQuery.AppendFormat("ORDER BY {0} {1}) r ", sortName, sortOrder);
						sbQuery.AppendFormat("ORDER BY {0} DESC) r ", sortName);
						sbQuery.AppendFormat("ORDER BY {0}", sortName);
						cmd.CommandText = sbQuery.ToString();
						cmd.CommandType = CommandType.Text;

                        //StringBuilder sbQuery = new StringBuilder();
                        //sbQuery.AppendFormat(" SELECT * ");
                        //sbQuery.AppendFormat(" FROM (SELECT TOP({0}) ", pageSize);
                        //sbQuery.AppendFormat("          r.* ");
                        //sbQuery.AppendFormat("       FROM (SELECT TOP({0} + {1} - 1) CONFIGNAME, CONFIGVALUE ", startRow, pageSize);
                        //sbQuery.AppendFormat("              FROM STP_CONFIG ");
                        //sbQuery.AppendFormat("             {0} ", parameters);
                        //sbQuery.AppendFormat("              ORDER BY {0} {1}) r ", sortName, sortOrder);
                        //sbQuery.AppendFormat("       ORDER BY {0} DESC) r ", sortName);
                        //sbQuery.AppendFormat(" ORDER BY {0}", sortName);


						try
						{
							conn.Open();
							using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
							{
								while (dr.Read())
								{
									stpReason t = new stpReason();
									t.reasonid = dr[stpReason.Fields.reasonid] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasonid].ToString();
									t.reasondesc = dr[stpReason.Fields.reasondesc] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasondesc].ToString();


									tt.Add(t);
									t.Dispose();
								}
							}
						}
						catch (Exception ex)
							{
								errorLog = ex.Message;
							}
						finally
							{
								if (conn.State == ConnectionState.Open) conn.Close();
							}
					}
				}
				return tt;
			}


			public stpReason GetById(string key)
			{
				stpReason  t = new stpReason();
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = " SELECT ReasonID, ReasonDesc FROM STP_REASON WHERE ReasonID = @reasonid";
						cmd.CommandType = CommandType.Text;
						cmd.Parameters.Add("@reasonid", SqlDbType.NChar).Value = key;
						try
						{
							conn.Open();
							using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
							{
								while (dr.Read())
								{
									t.reasonid = dr[stpReason.Fields.reasonid] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasonid].ToString();
									t.reasondesc = dr[stpReason.Fields.reasondesc] == DBNull.Value ? string.Empty : dr[stpReason.Fields.reasondesc].ToString();
								}
							}
						}
						catch (Exception ex)
						{
							errorLog = ex.Message;
						}
						finally
						{
							if (conn.State == ConnectionState.Open) conn.Close();
						}
					}
				}
			return t;
			}


			public bool Save(stpReason domain)
			{
				bool result = false;
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = @" INSERT INTO STP_REASON(
ReasonID, 
ReasonDesc
)
VALUES(
@reasonid
, @reasondesc
)";
						cmd.CommandType = CommandType.Text;
						cmd.Parameters.Add("@reasonid", SqlDbType.NChar).Value =domain.reasonid;
						cmd.Parameters.Add("@reasondesc", SqlDbType.VarChar).Value =domain.reasondesc;
						try
						{
							conn.Open();
							cmd.ExecuteNonQuery();
							result = true;
						}
						catch (Exception ex)
						{
							errorLog = ex.Message;
						}
						finally
						{
							if (conn.State == ConnectionState.Open) conn.Close();
						}
					}
				}
			return result;
			}


			public bool Update(stpReason domain)
			{
				int result = 0;
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = @"UPDATE STP_REASON SET 
ReasonID = @reasonid
, ReasonDesc = @reasondesc
 WHERE ReasonID= @reasonid";
						cmd.CommandType = CommandType.Text;
						cmd.Parameters.Add("@reasonid", SqlDbType.NChar).Value =domain.reasonid;
						cmd.Parameters.Add("@reasondesc", SqlDbType.VarChar).Value =domain.reasondesc;


						try
						{
							conn.Open();
							result = cmd.ExecuteNonQuery();
						}
						catch (Exception ex)
						{
							errorLog = ex.Message;
						}
						finally
						{
							if (conn.State == ConnectionState.Open) conn.Close();
						}
					}
				}
			return (result > 0);
			}


			public bool Delete(string key)
			{
				bool result = false;
				using (SqlConnection conn = new SqlConnection(connectionString))
				{
					using (SqlCommand cmd = conn.CreateCommand())
					{
						cmd.CommandText = "DELETE FROM STP_REASON WHERE ReasonID= @reasonid";
						cmd.CommandType = CommandType.Text;
						cmd.Parameters.Add("@reasonid", SqlDbType.Int).Value = key;


						try
						{
							conn.Open();
							cmd.ExecuteNonQuery();
							result = true;
						}
						catch (Exception ex)
						{
							errorLog = ex.Message;
						}
						finally
						{
							if (conn.State == ConnectionState.Open) conn.Close();
						}
					}
				}
			return result;
			}


			public string GetErrorMessage()
			{
				return errorLog;
			}


		}
	}
